﻿using CsvHelper;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Text;
using System.Dynamic;

namespace Exhort.Utility.Helper
{
    /// <summary>
    /// Nopi电子表格处理
    /// </summary>
    public static class NpoiHelper
    {
        /// <summary>
        /// 尝试设置工作薄指定列的宽度
        /// </summary>
        /// <param name="sheet">工作薄对象</param>
        /// <param name="his">各列内容最大字节长度的集合</param>
        /// <param name="colIndex">列索引</param>
        /// <param name="val">单元格内容</param>
        private static void TrySetSheetColumnWidth(ISheet sheet, long[] his, int colIndex, string val)
        {
            long cellValueByteLength = GetByteLength(val);
            if (cellValueByteLength > his[colIndex])
            {
                his[colIndex] = cellValueByteLength;
                sheet.SetColumnWidth(colIndex, 256 * GetHeadCellByteLength(his[colIndex]));
            }
        }

        /// <summary>
        /// 获取字符串的实际字节数
        /// </summary>
        /// <param name="value">要获取长度的字符串</param>
        /// <returns></returns>
        private static long GetByteLength(string value)
        {
            if (value.Equals(string.Empty))
            { return 0; }
            int strlen = 0;
            ASCIIEncoding strData = new ASCIIEncoding();
            //将字符串转换为ASCII编码的字节数字
            byte[] strBytes = strData.GetBytes(value);
            for (int i = 0; i <= strBytes.Length - 1; i++)
            {
                if (strBytes[i] == 63)  //中文都将编码为ASCII编码63,即"?"号
                { strlen++; }
                strlen++;
            }
            return strlen;
        }

        private static int GetHeadCellByteLength(long byteLength)
        {
            int length = ((int)byteLength + 1);
            return length <= 150 ? length : 150;
        }

        /// <summary>
        /// Excel文件导成Datatable
        /// </summary>
        /// <param name="strFilePath">Excel文件目录地址</param>
        /// <param name="strTableName">Datatable表名</param>
        /// <param name="iSheetIndex">Excel sheet index</param>
        /// <returns></returns>
        public static DataTable XlSToDataTable(string strFilePath, string strTableName, int iSheetIndex)
        {
            string strExtName = Path.GetExtension(strFilePath);

            DataTable dt = new DataTable();

            if (!string.IsNullOrEmpty(strTableName))
            {
                dt.TableName = strTableName;
            }

            if (strExtName.Equals(".xls") || strExtName.Equals(".xlsx"))
            {
                using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;

                    switch (strExtName)
                    {
                        case ".xls": workbook = new HSSFWorkbook(file); break;
                        case ".xlsx": workbook = new XSSFWorkbook(file); break;
                        default: throw new Exception("未能识别的文件类型");
                    }

                    ISheet sheet = workbook.GetSheetAt(iSheetIndex);

                    //列头
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        dt.Columns.Add(item.ToString(), typeof(string));
                    }

                    //写入内容
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    while (rows.MoveNext())
                    {
                        IRow row = (IRow)rows.Current;
                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }

                        DataRow dr = dt.NewRow();
                        foreach (ICell item in row.Cells)
                        {
                            switch (item.CellType)
                            {
                                case CellType.Boolean:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.Error:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.Formula:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.Boolean:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.Error:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = item.NumericCellValue;
                                            }
                                            break;
                                        case CellType.String:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.Blank:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = item.NumericCellValue;
                                    }
                                    break;
                                case CellType.String:
                                    string strValue = item.StringCellValue;
                                    if (!string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = null;
                                    }
                                    break;
                                case CellType.Unknown:
                                case CellType.Blank:
                                default:
                                    dr[item.ColumnIndex] = string.Empty;
                                    break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }

            return dt;
        }

        public static void DataTableToXls(DataTable dt, string sheetName, string filename)
        {
            //创建Excel
            HSSFWorkbook book = new HSSFWorkbook();
            //创建工作薄
            ISheet sheet = book.CreateSheet(sheetName);
            //格式
            ICellStyle dateStyle = book.CreateCellStyle();
            IDataFormat format = book.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
            //创建标题行
            IRow row = sheet.CreateRow(0);
            ICell cell = null;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }
            //创建内容行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);
                    string drValue = dt.Rows[i][j].ToString();

                    switch (dt.Rows[i][j].GetType().ToString())
                    {
                        case "System.String"://字符串类型
                            cell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            cell.SetCellValue(dateV);
                            cell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            cell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            cell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            cell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            cell.SetCellValue("");
                            break;
                        default:
                            cell.SetCellValue("");
                            break;
                    }
                }
            }
            using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
            {
                book.Write(fs);
            }
        }

        #region CVS

        public static DataTable CsvToDataTable(string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                using (StreamReader sr = new StreamReader(fs, Encoding.Default))
                {
                    using (CsvReader cr = new CsvReader(sr, CultureInfo.CurrentCulture))
                    {
                        var records = cr.GetRecords<dynamic>().ToList();

                        DataTable dt = new DataTable();

                        if (records.Count > 0)
                        {
                            IDictionary<string, object> id = records[0];

                            foreach (var key in id.Keys)
                            {
                                dt.Columns.Add(key);
                            }

                            foreach (var item in records)
                            {
                                IDictionary<string, object> idic = item;

                                DataRow row = dt.NewRow();

                                foreach (var key in id.Keys)
                                {
                                    row[key] = idic[key];
                                }

                                dt.Rows.Add(row);
                            }
                        }

                        return dt;
                    }
                }
            }
        }

        public static void DataTableToCsv(DataTable dt, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                using (StreamWriter sw = new StreamWriter(fs, Encoding.Default))
                {
                    using (CsvWriter cr = new CsvWriter(sw, CultureInfo.CurrentCulture))
                    {
                        var modelList = new List<dynamic>();

                        foreach (DataRow row in dt.Rows)
                        {
                            dynamic model = new ExpandoObject();

                            IDictionary<string, object> dict = model;

                            foreach (DataColumn column in dt.Columns)
                            {
                                dict[column.ColumnName] = row[column];
                            }
                            modelList.Add(model);
                        }

                        cr.WriteRecords(modelList);
                    }
                }
            }
        }

        #endregion
    }
}
